package com.yenlien.traceyun.plant.dao;

import com.yenlien.traceyun.plant.entity.Inputs;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface InputsDao extends BaseJpaDao<Inputs> {

    Inputs findById(String id);

    @Transactional
    @Modifying
    @Query("update Inputs input set input.isDelete=true where input.id=?1")
    public void ljDelete(String id);

    @Query(value = "select input from Inputs input where input.isDelete=false")
    public List<Inputs> findNotDeleteAll();

//	@Query(value = "SELECT ti.* FROM t_inputs ti LEFT JOIN dock_zhongyang dz ON ti.id = dz.process_plant_id JOIN t_company tc ON ti.company_id = tc.id " +
//			"WHERE dz.id IS NULL AND ISNULL(tc.credit_code) = 0 and LENGTH(trim(tc.credit_code)) > 0 AND tc.id = \"2c9d0ded78b625970178bb8d5ac8445\"", nativeQuery = true)
//	List<Inputs> getSnPlantProcess();

    // 加了 @Transient 后就封装不上值该字段的值
    // 获取可以对接却还未对接的数据
    @Query(value = "SELECT tpi.* FROM t_plant_inputs tpi JOIN t_company tc ON tpi.company_id = tc.id " +
            "LEFT JOIN dock_zhongyang dz ON tpi.id = dz.process_plant_id WHERE dz.id IS NULL AND ISNULL( tc.credit_code ) = 0  AND LENGTH( trim( tc.credit_code ) ) >0", nativeQuery = true)
    List<Inputs> getSnPlantProcess();


    @Query(value = "SELECT * FROM t_plant_inputs WHERE company_id IN (SELECT id FROM t_company WHERE parent_id = ?1 OR id = ?1) ORDER BY createtime DESC", nativeQuery = true)
    List<Inputs> getByCompanyId(String companyId);

    @Query(value = "SELECT * FROM t_plant_inputs WHERE source = '购买'  AND buy_time BETWEEN ?1 AND ?2 AND company_id = ?3", nativeQuery = true)
    List<Inputs> getCost(String startTime, String endTime, String companyId);

    @Query(value = "SELECT * FROM t_plant_inputs WHERE source = '购买'  AND buy_time BETWEEN ?1 AND ?2 AND company_id = ?3 AND type = ?4", nativeQuery = true)
    List<Inputs> getCostType(String startTime, String endTime, String companyId, String type);
}
